# -*- coding:utf-8 -*-

from typing import List, Union
from datetime import datetime, time
from module_admin.entity.do.role_do import SysRoleDept
from sqlalchemy import and_, delete, desc, func, or_, select, update
from sqlalchemy.ext.asyncio import AsyncSession
from module_gen.constants.gen_constants import GenConstants
from module_doc.entity.do.bg_file_do import BgFile
from module_doc.entity.do.bg_file_category_do import BgFileCategory
from module_doc.entity.vo.bg_file_vo import BgFilePageModel, BgFileModel
from utils.page_util import PageUtil, PageResponseModel
from utils.common_util import CamelCaseUtil


class BgFileDao:

    @classmethod
    async def get_by_id(cls, db: AsyncSession, bg_file_id: int) -> BgFile:
        """根据主键获取单条记录"""
        bg_file = (((await db.execute(
                            select(BgFile)
                            .where(BgFile.id == bg_file_id)))
                       .scalars())
                       .first())
        return bg_file

    @classmethod
    async def get_all_subcategory_ids(cls, db: AsyncSession, category_id: int) -> List[int]:
        """
        递归获取指定分类及其所有子分类的ID列表
        """
        all_category_ids = [category_id]  # 包含当前分类ID
        
        # 递归获取所有子分类
        def get_subcategories(parent_id: int):
            query = select(BgFileCategory.id).where(
                BgFileCategory.parent_id == parent_id,
                BgFileCategory.del_flag == '0'
            )
            return query
        
        # 使用递归查询获取所有子分类
        async def fetch_all_subcategories(parent_id: int):
            result = await db.execute(get_subcategories(parent_id))
            subcategory_ids = [row[0] for row in result.fetchall()]
            
            for sub_id in subcategory_ids:
                all_category_ids.append(sub_id)
                # 递归获取子分类的子分类
                await fetch_all_subcategories(sub_id)
        
        await fetch_all_subcategories(category_id)
        return all_category_ids

    """
    查询
    """
    @classmethod
    async def get_bg_file_list(cls, db: AsyncSession,
                             query_object: BgFilePageModel,
                             data_scope_sql: str = None,
                             is_page: bool = False) -> Union[List, PageResponseModel]:

        # 如果指定了分类ID，获取该分类及其所有子分类的ID列表
        category_ids = None
        if query_object.category_id:
            category_ids = await cls.get_all_subcategory_ids(db, query_object.category_id)

        query = (
            select(BgFile.__table__.c, BgFileCategory.category_name)
            .outerjoin(BgFileCategory, BgFile.category_id == BgFileCategory.id)
            .where(
                BgFile.category_id.in_(category_ids) if category_ids else True,
                BgFile.name.like(f"%{query_object.name}%") if query_object.name else True,
                BgFile.is_vip == query_object.is_vip if query_object.is_vip is not None else True,
                BgFile.del_flag == '0',
                eval(data_scope_sql) if data_scope_sql else True,
            )
            .order_by(desc(BgFile.create_time))
            .distinct()
        )
        bg_file_list = await PageUtil.paginate(db, query, query_object.page_num, query_object.page_size, is_page)
        
        # 处理关联数据
        # if isinstance(bg_file_list, list):
        #     for item in bg_file_list:
        #         if hasattr(item, '__dict__'):
        #             file_dict = CamelCaseUtil.transform_result(item[0])
        #             file_dict['categoryName'] = item[1] if item[1] else ''
        #             bg_file_list[bg_file_list.index(item)] = file_dict
        # elif hasattr(bg_file_list, 'rows'):
        #     for i, item in enumerate(bg_file_list.rows):
        #         if hasattr(item, '__dict__'):
        #             file_dict = CamelCaseUtil.transform_result(item[0])
        #             file_dict['categoryName'] = item[1] if item[1] else ''
        #             bg_file_list.rows[i] = file_dict
        #
        return bg_file_list


    @classmethod
    async def add_bg_file(cls, db: AsyncSession, add_model: BgFileModel, auto_commit: bool = True) -> BgFileModel:
        """
        增加
        """
        bg_file =  BgFile(**add_model.model_dump(exclude_unset=True, ))
        db.add(bg_file)
        await db.flush()
        bg_file_model = BgFileModel(**CamelCaseUtil.transform_result(bg_file))
        if auto_commit:
            await db.commit()
        return bg_file_model

    @classmethod
    async def edit_bg_file(cls, db: AsyncSession, edit_model: BgFileModel, auto_commit: bool = True) -> BgFile:
        """
        修改
        """
        edit_dict_data = edit_model.model_dump(exclude_unset=True, exclude={ *GenConstants.DAO_COLUMN_NOT_EDIT })
        await db.execute(update(BgFile), [edit_dict_data])
        await db.flush()
        if auto_commit:
            await db.commit()
        return await cls.get_by_id(db, edit_model.id)

    @classmethod
    async def del_bg_file(cls, db: AsyncSession, bg_file_ids: List[str], soft_del: bool = True, auto_commit: bool = True):
        """
        删除
        """
        if soft_del:
            await db.execute(update(BgFile).where(BgFile.id.in_(bg_file_ids)).values(del_flag='2'))
        else:
            await db.execute(delete(BgFile).where(BgFile.id.in_(bg_file_ids)))
        await db.flush()
        if auto_commit:
            await db.commit()